Excel Searchable Table/Database
You might have an Excel spreadsheet with hundreds or even thousands of rows of people and their contact information. Maybe you have to find one specific person on this spreadsheet. It is true that you can use the Control f (find command) to find the person, or there is a more advanced method that allows you to just display that person. It is possible that you will need to display information only for people from a specific state or city. The Find command will not do this. A table can be created that will allow you to sort or display items by specific attributes such as age, city, state, gender, and so on. On any row you can make what appear to be titles for each area of information. For example, going left to right, "First Name, Last Name, Age" (without the quotes). Next, you can put in information in the rows beneath your titles. Finally, you can highlight the titles and information you have put it the rows, and then press Control t to bring up a box for creating a table. You will hear the range of cells you have highlighted. Tab one time and you will hear something like "check box checked My Table has Headers." Make sure this check box is checked, and then hit the Enter key one time. Move to the first cell or title of your table. Press and hold down the Alt key and hit the down arrow key one time. A menu drops down. You can down arrow through this menu to see what is there. In this menu will be a search box. In this box type a name that is somewhere in the column you are in. Hit the Enter key one time. Down arrow to see if the name and other information is there. Move back to the title cell, also known as a header. Press and hold down the Alt key and hit the down arrow key. Down arrow to the menu item to "Clear filer from..." and hit the Enter key one time. All of your contents will be displayed once again. You can use this technique to display all rows or records by city, state, age gender, and so on. So, you might search for all males, and then move to the header for age and search for a specific age. You will then have displayed information for all males of a specific age. Remember to "Clear filter from..." for all headers that you searched. Try the following activity to develop the skill of creating a searchable table/database in Excel. When you are finished, move to the link titled "BACK" at the bottom of this page to move to the "Excel Lessons" web page.
NOTE: You can write titles or headers in a row just above a group of rows with existing content, highlight the titles/headers and content, and the press Control t to create a table. It is a quick and instant method for being able to sort or search large amounts of content.
1. Open Excel.
2. Type "First Name" (without the quotes) in cell a1.
3. Hit the Tab key one time.
4. Type "Age" (without the quotes) in cell b1.
5. Hit the Tab key one time.
6. Type "City" (without the quotes) in cell c1.
7. Move to cell a2.
8. Type "John" (without the quotes.
9. Hit the Tab key one time.
10. Type the number "30" (without the quotes).
11. Hit the Tab key one time.
12. Type "Indianapolis" (without the quotes).
13. Move to cell a3.
14. Type "Sue" (without the quotes).
15. Hit the Tab key one time.
16. Type the number "25" (without the quotes).
17. Hit the Tab key one time.
18. Type "Spencer" (without the quotes).
19. Move to cell a4.
20. Type "Tom" (without the quotes).
21. Hit the Tab key one time.
22. Type the number "30" (without the quotes).
23. Hit the Tab key one time.
24. Type "Spencer" (without the quotes).
25. Move to cell a1.
26. Press and hold down the Control and Shift keys and hit the right arrow to highlight the titles/headers, and then down arrow to highlight the rows of information.
27. Release the keys.
28. Press and hold down the Control key and hit the letter t.
29. Tab to the checkbox that says "My table has headers" and make sure that it is checked.
30. Hit the Enter key one time.
31. Move to cell a1 there the "First Name" title/header is.
32. Press and hold down the Alt key and hit the down arrow key one time.
33. Down arrow to the search box.
34. Type "Tom" (without the quotes).
35. Hit the Enter key one time.
36. Down arrow and you should hear information about "Tom."
37. Move back to the "First Name" title/header.
38. Alt down arrow.
39. Release the keys.
40. Down arrow to "Clear filter from..."
41. Hit the Enter key one time.
42. Move to the "Age" title/header.
43. Alt down arrow.
44. Release the keys.
45. Down arrow to the search box.
46. Type the number "30" (without the quotes).
47. Hit the Enter key one time.
48. Down arrow and there should be two rows of information. You sorted or searched for "30," and John and Tom both had "30" for their age.
49. Move back to the "Age" title/header.
50. Alt down arrow.
51. Release the keys.
52. Down arrow to "Clear filter from..."
53. Hit the Enter key one time and all three rows of content appear.
54. Practice the above steps until you are quick and accurate.
55. Add more people to your table, and then practice more searches. Make sure to "Clear filter from..." after each search.
Challenge: If you have an online bank account, copy paste your ledge into Excel, on Row 2, then make titles/headers on Row 1, highlight the titles/headers and the content, make the table with headers, and then try tasks such as searching or sorting by date.